----------------------------------- LINH VUC KINH DOANH-------------------

ALTER PROCEDURE spLinhVucKD_GetAllAvailable
@Lang varchar(50),
@PortalID int
AS
SELECT * FROM CMS_LinhVucKinhDoanh
WHERE Lang = @Lang AND TrangThai = 1 AND PortalID = @PortalID
ORDER BY ThuTu ASC, CreateDate DESC

GO

ALTER PROCEDURE spLinhVucKD_GetAll
@Lang varchar(50),
@PortalID int
AS
SELECT * FROM CMS_LinhVucKinhDoanh
WHERE Lang = @Lang AND PortalID = @PortalID
ORDER BY ThuTu ASC, CreateDate DESC

GO

ALTER PROCEDURE spLinhVucKD_GetAllByFilter
@Lang varchar(50),
@Filter bit,
@PortalID int
AS
BEGIN
	SELECT * FROM CMS_LinhVucKinhDoanh
	WHERE Lang = @Lang and TrangThai = @Filter and PortalID = @PortalID
	ORDER BY ThuTu ASC, CreateDate DESC
END
GO

ALTER PROCEDURE spLinhVucKD_GetInfo
@LinhVucID int
AS
SELECT * FROM dbo.CMS_LinhVucKinhDoanh
WHERE LinhVucID = @LinhVucID

GO
ALTER PROCEDURE dbo.spLinhVucKD_SetIndex
	@LINHVUCID	int,
	@Index 	int
AS
UPDATE dbo.CMS_LinhVucKinhDoanh SET ThuTu = @Index  WHERE LinhVucID = @LINHVUCID

GO

ALTER PROCEDURE dbo.spLinhVucKD_SetStatus
	@LINHVUCID	int,
	@Status 	int
AS
UPDATE dbo.CMS_LinhVucKinhDoanh SET TrangThai = @Status  WHERE LinhVucID = @LINHVUCID

GO

ALTER PROCEDURE [dbo].[spLinhVucKD_Insert]
(
	@CapChaID int,
	@TenLinhVuc nvarchar(250),	
	@NoiDung ntext,
	@TrangThai bit,
	@ThuTu int,	
	@CreateUserID int,
	@PortalID int,
	@Lang varchar(50)
)
AS
INSERT INTO CMS_LinhVucKinhDoanh
(
	CapChaID,
	TenLinhVuc,
	NoiDung,
	TrangThai,
	ThuTu,
	CreateDate,
	CreateUserID,
	PortalID,
	Lang
)
VALUES 
(
	@CapChaID,
	@TenLinhVuc,
	@NoiDung,
	@TrangThai,
	@ThuTu,
	GETDATE(),
	@CreateUserID,
	@PortalID,
	@Lang

)
DECLARE @RETURN_ID INT
SET @RETURN_ID = SCOPE_IDENTITY()
SELECT @RETURN_ID

GO

ALTER PROCEDURE [dbo].[spLinhVucKD_Update]
(
	@LinhVucID int,
	@CapChaID int,
	@TenLinhVuc nvarchar(250),	
	@NoiDung ntext,
	@TrangThai bit,
	@ThuTu int,	
	@ModifyUserID int,
	@PortalID int,
	@Lang varchar(50)	
)
AS
UPDATE dbo.CMS_LinhVucKinhDoanh SET
	CapChaID = @CapChaID,
	TenLinhVuc = @TenLinhVuc,
	NoiDung = @NoiDung,
	TrangThai = @TrangThai,
	ThuTu = @ThuTu,
	ModifyDate = GETDATE(),
	ModifyUserID = @ModifyUserID,
	PortalID = @PortalID,
	Lang = @Lang	

WHERE LinhVucID = @LinhVucID

GO

ALTER PROCEDURE [dbo].spLinhVucKD_Delete
@LinhVucID int
AS
BEGIN
	SET NOCOUNT ON;

delete dbo.CMS_LinhVucKinhDoanh
 where LinhVucID = @LinhVucID

END

GO

ALTER PROCEDURE spLinhVucKD_Count
@Lang varchar(50),
@PortalID int	
AS
BEGIN
	SELECT COUNT(*) AS 'COUNT' FROM CMS_LinhVucKinhDoanh WHERE Lang = @Lang and PortalID = @PortalID
END

GO

ALTER PROCEDURE sp_LinhVucKinhDoanhParentChildren
@id int,
@Lang varchar(50),
@PortalID int
as
begin
with x
	as
	(
	   select
		  LinhVucID, CapChaID, TenLinhVuc, Lang, ThuTu, PortalID
	   from
		  CMS_LinhVucKinhDoanh
	   where
		  LinhVucID = @ID
	   union all
	   select
		  m.LinhVucID, m.CapChaID, m.TenLinhVuc, m.Lang, m.ThuTu, m.PortalID
	   from
		  CMS_LinhVucKinhDoanh m
	   join
		  x on x.LinhVucID = m.CapChaID
	)
	select *
	from x
	where x.Lang = @Lang and x.PortalID = @PortalID
	order by ThuTu asc
end

GO

--ALTER PROC spLinhVucKinhDoanhChildrenByParent
--@CapChaID int,
--@Lang varchar(50)
--AS
--BEGIN
--	SELECT * FROM CMS_LinhVucKinhDoanh
--	WHERE CapChaID = @CapChaID and Lang = @Lang
--	ORDER BY THUTU
--END

ALTER PROCEDURE spGetByParentIDAndPortalID
(
@ParentID int,
@PortalID int,
@Lang varchar(50)
)
AS
begin
	SELECT     dbo.CMS_LinhVucKinhDoanh.*
	FROM       dbo.CMS_LinhVucKinhDoanh
	WHERE CapChaID = @ParentID And Lang = @Lang And PortalID = @PortalID
	ORDER BY ThuTu
end

GO
-----------------------------------END LINH VUC KINH DOANH-----------------------------